﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spRest_ByLabel]
#-- Purpose:		Provides a mechanism to restore a database to a label
#--					via stored procedure call
#--	Last Update:	04/23/2013
#--					For a complete history - please review comments in SVN
#-- Called By:		Restore Database Utility Executable
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spRest_ByLabel]
(
	@database_name			sysname,
	@restore_label			nvarchar(255)	= NULL,
	@stopat_time			datetime		= NULL,
	@restore_now			bit				= 0,
	@verify_only			bit				= 0,
	@destination_db			sysname			= NULL,
	@skip_pre_backup		bit				= 0
)
--WITH EXECUTE AS OWNER
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE	@sError varchar(1000), @last_backup_set_id int

--- Validate Parameters
IF @skip_pre_backup IS NULL
	SET	@skip_pre_backup = 0

--- Set a default value of Now() for @stopat_time if not passed in
IF @stopat_time IS NULL
	SET	@stopat_time = GETDATE()

--- Get the latest backup with the given label within 6 hours of the requested date stamp
SELECT		@last_backup_set_id = MAX(backup_set_id)
FROM		[msdb].[dbo].[backupset]
WHERE		name = @restore_label
			AND database_name = @database_name
			AND backup_start_date >= DATEADD(hour, -6, @stopat_time)
			AND backup_start_date <= DATEADD(hour, +6, @stopat_time)
			AND is_copy_only = 0

--- Error if no backups with this label exist
IF @last_backup_set_id IS NULL
  BEGIN
	SELECT	@sError = 'No backups of ' + QUOTENAME(@database_name) + ' were found with the label ' + QUOTENAME(@restore_label)
	RAISERROR (@sError, 16, 1)
  END

--- Execute the Restore
EXEC	[dbo].[spRest_ByBackupSetID]	@database_name = @database_name, @last_backup_set_id = @last_backup_set_id,
										@restore_now = @restore_now, @verify_only = @verify_only, @destination_db = @destination_db,
										@skip_pre_backup = @skip_pre_backup

SET NOCOUNT OFF
